package com.ms.orm.dboperator;

import com.ms.common.exception.BaseException;
import com.ms.common.exception.ServiceException;
import com.ms.common.map.CaseInsensitiveMap;
import com.ms.common.util.*;
import com.ms.orm.annotation.TableField;
import com.ms.orm.exception.BaseSqlException;
import com.ms.orm.exception.OrmException;
import com.ms.orm.mapping.ResultSetMapper;
import com.ms.orm.meta.JavaTypeMapperJdbcType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.util.*;

/**
 * @author zdh
 */
public class DbCommonUtil {



    private static final Logger logger = LoggerFactory.getLogger(DbCommonUtil.class);


    /**
     * @param preparedStatement
     * @param index
     * @param value
     * @param sqlType
     * @throws SQLException
     */
    public static void setPreparedStatementParameter(PreparedStatement preparedStatement, int index, Object value, int sqlType) throws SQLException{
        if(value==null){
            if(sqlType==JavaTypeMapperJdbcType.TYPE_UNKNOW){
                preparedStatement.setNull(index, Types.NULL);
            }
            else{
                preparedStatement.setNull(index, sqlType);
            }
        }
        else{
            if(sqlType==Types.VARCHAR){
                preparedStatement.setString(index, value.toString());
            }
            else if(sqlType==Types.DECIMAL || sqlType==Types.NUMERIC){
                if(value instanceof BigDecimal){
                    preparedStatement.setBigDecimal(index, (BigDecimal)value);
                }
                else{
                    preparedStatement.setObject(index, value, sqlType);
                }
            }
            else if(sqlType==Types.DATE){
                if(value instanceof Date){
                    preparedStatement.setDate(index, new java.sql.Date(((Date)value).getTime()));
                }
                else if(value instanceof Calendar){
                    preparedStatement.setDate(index, new java.sql.Date(((Calendar)value).getTime().getTime()));
                }
                else{
                    preparedStatement.setObject(index, value, Types.DATE);
                }
            }
            else if(sqlType==Types.TIME){
                if(value instanceof Date){
                    preparedStatement.setTime(index, new java.sql.Time(((Date)value).getTime()));
                }
                else if(value instanceof Calendar){
                    preparedStatement.setTime(index, new java.sql.Time(((Calendar)value).getTime().getTime()));
                }
                else{
                    preparedStatement.setObject(index, value, Types.TIME);
                }
            }
            else if(sqlType==Types.TIMESTAMP){
                if(value instanceof Date){
                    preparedStatement.setTimestamp(index, new java.sql.Timestamp(((Date)value).getTime()));
                }
                else if(value instanceof Calendar){
                    preparedStatement.setTimestamp(index, new java.sql.Timestamp(((Calendar)value).getTime().getTime()));
                }
                else{
                    preparedStatement.setObject(index, value, Types.TIMESTAMP);
                }
            }
            else if(sqlType== JavaTypeMapperJdbcType.TYPE_UNKNOW){
                if(value instanceof String){
                    preparedStatement.setString(index, value.toString());
                }
                else if(isDateValue(value)){
                    preparedStatement.setTimestamp(index, new java.sql.Timestamp(((Date)value).getTime()));
                }
                else{
                    preparedStatement.setObject(index, value);
                }
            }
            else{
                preparedStatement.setObject(index, value, sqlType);
            }
        }
    }

    /**
     * 设置 PreparedStatement 指定位置值
     * @param preparedStatement
     * @param index
     * @param value
     */
    public static void setPreparedStatementParameterWithoutSqlType(PreparedStatement preparedStatement, int index, Object value) {
        try {
            _setPreparedStatementParameterWithoutSqlType(preparedStatement, index, value);
        } catch (Exception e) {
            throw new BaseSqlException("set sql type error.", e);
        }
    }

    //存在问题？？
   /* public static void setPreparedStatementParameter(PreparedStatement pstm, int index, Object value) {
        try {
            setPreparedStatementParameter(pstm, index, value, JavaTypeMapperJdbcType.getSqlType(value));
        } catch (Exception e) {
            throw new BaseSqlException("setPreparedStatementParameter error : " + e.getMessage(), e);
        }
    }*/

    private static void _setPreparedStatementParameterWithoutSqlType(PreparedStatement pstm, int index, Object value) throws SQLException{
        if(value==null){
            //1、设置空值
            pstm.setNull(index, Types.NULL);
        }
        else{
            if(value instanceof String){
                //2、String
                pstm.setString(index, value.toString());
            }
            else if(value instanceof BigDecimal){
                //3、BigDecimal
                pstm.setBigDecimal(index, (BigDecimal)value);
            }
            else if(value instanceof Integer){
                //4、Integer
                pstm.setInt(index, ((Integer)value).intValue());
            }
            else if(value instanceof Float){
                //4、Integer
                pstm.setFloat(index, ((Float)value).floatValue());
            }
            else if(value instanceof Double){
                //5、Double
                pstm.setDouble(index, ((Double)value).doubleValue());
            }
            else if(value instanceof java.time.LocalDateTime){
                //6、LocalDateTime
               long milliseconds = ((LocalDateTime)value).atZone(ZoneId.systemDefault()).toInstant().toEpochMilli();
                pstm.setTimestamp(index, new java.sql.Timestamp(milliseconds));
            }
            else if(value instanceof java.time.LocalDate){
                //7、LocalDate
                LocalDate currentDate = (LocalDate)value;
                LocalDateTime dateTime = LocalDateTime.of(currentDate, LocalTime.MIN);
                long milliseconds = dateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli();
                pstm.setDate(index, new java.sql.Date(milliseconds));
            }
            else if(isDateValue(value)){
                //8、Timestamp
                pstm.setTimestamp(index, new java.sql.Timestamp(((java.util.Date)value).getTime()));
            }
            else if(value instanceof java.util.Calendar){
                //9、Calendar
                pstm.setTimestamp(index, new java.sql.Timestamp(((java.util.Calendar)value).getTime().getTime()));
            }
            else if(value instanceof java.sql.Date){
                //10、java.sql.Date
                pstm.setDate(index, new java.sql.Date(((java.sql.Date)value).getTime()));
            }
            else if(value instanceof java.sql.Time){
                //11、java.sql.Time
                pstm.setTime(index, new java.sql.Time(((java.sql.Date)value).getTime()));
            }
            else if(value instanceof java.sql.Timestamp){
                //12、java.sql.Time
                pstm.setTimestamp(index, new java.sql.Timestamp(((java.util.Date)value).getTime()));
            }
            else{
                //13、
                pstm.setObject(index, value);
            }
        }
    }



    public static String addStatementParameter(String column, Object value){
        if(column==null || column.length()<1 || value==null) {
            return "";
        }
        StringBuffer sql = new StringBuffer();
        sql.append(column);
        sql.append("=");
        sql.append(getValueSQLString(value));

        return sql.toString();
    }

    /**
     * 获取
     * @param value
     * @return
     */
    public static String getValueSQLString(Object value){
        if(value==null) {
            return "''";
        }
        StringBuffer sql = new StringBuffer();
        if(value instanceof Number){
            sql.append(value);
        }
        else if(isDateValue(value) || value instanceof java.sql.Timestamp){
            sql.append("'");
            sql.append(DateUtil.formatDateTime((Date)value));
            sql.append("'");
        }
        else if(value instanceof java.sql.Date){
            sql.append("'");
            sql.append(DateUtil.formatDate((Date)value));
            sql.append("'");
        }
        else if(value instanceof java.sql.Time){
            sql.append("'");
            sql.append(DateUtil.formatTime((Date)value));
            sql.append("'");
        }
        else {
            sql.append("'");
            sql.append(value.toString().replace("'", ""));
            sql.append("'");
        }

        return sql.toString();
    }

    public static Object getResultSetValue(ResultSet rs, String colName) throws SQLException {
        Object obj = rs.getObject(colName);
        if (obj instanceof Blob) {
            obj = rs.getBytes(colName);
        }
        else if (obj instanceof Clob) {
            obj = rs.getString(colName);
        }
        else if (obj != null && obj.getClass().getName().startsWith("oracle.sql.TIMESTAMP")) {
            obj = rs.getTimestamp(colName);
        }
        else if (obj != null && obj.getClass().getName().startsWith("oracle.sql.DATE")) {
            obj = rs.getDate(colName);
        }
        else if (obj != null && obj instanceof java.sql.Date) {
            obj = rs.getDate(colName);
        }
        return obj;
    }

    public static Object getValueByFieldFromResultSet(String colName, Class requiredType, ResultSet rs) throws SQLException{
        if (requiredType == null) {
            return getResultSetValue(rs, colName);
        }
        if(requiredType.isArray()) {
            requiredType = requiredType.getComponentType();
        }

        Object value = null;
        boolean wasNullCheck = false;

        // Explicitly extract typed value, as far as possible.
        if (String.class.equals(requiredType)) {
            value = rs.getString(colName);
        }
        else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {
            value = Boolean.valueOf(rs.getBoolean(colName));
            wasNullCheck = true;
        }
        else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {
            value = new Byte(rs.getByte(colName));
            wasNullCheck = true;
        }
        else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {
            value = new Short(rs.getShort(colName));
            wasNullCheck = true;
        }
        else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {
            value = new Integer(rs.getInt(colName));
            wasNullCheck = true;
        }
        else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {
            value = new Long(rs.getLong(colName));
            wasNullCheck = true;
        }
        else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {
            value = new Float(rs.getFloat(colName));
            wasNullCheck = true;
        }
        else if (double.class.equals(requiredType) || Double.class.equals(requiredType) ||
                Number.class.equals(requiredType)) {
            value = new Double(rs.getDouble(colName));
            wasNullCheck = true;
        }
        else if (byte[].class.equals(requiredType)) {
            value = rs.getBytes(colName);
        }
        else if (java.sql.Date.class.equals(requiredType)) {
            value = rs.getDate(colName);
        }
        else if (java.sql.Time.class.equals(requiredType)) {
            value = rs.getTime(colName);
        }
        else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) {
            value = rs.getTimestamp(colName);
        }
        else if (BigDecimal.class.equals(requiredType)) {
            value = rs.getBigDecimal(colName);
        }
        else if (Blob.class.equals(requiredType)) {
            value = rs.getBlob(colName);
        }
        else if (Clob.class.equals(requiredType)) {
            value = rs.getClob(colName);
        }
        else {
            // Some unknown type desired -> rely on getObject.
            value = getResultSetValue(rs, colName);
        }

        // Perform was-null check if demanded (for results that the
        // JDBC driver returns as primitives).
        if (wasNullCheck && value != null && rs.wasNull()) {
            value = null;
        }
        return value;
    }


    public static boolean isDateValue(Object inValue) {
        return (inValue instanceof java.util.Date && !(inValue instanceof java.sql.Date ||
                inValue instanceof java.sql.Time || inValue instanceof java.sql.Timestamp));
    }

    /**
     * 根据 ResultSet 和 bean 实例，设置实例值，
     * 这里可以使用 已处理的实体元数据进行处理，减少反射
     * @param rs
     * @param bean
     */
    public static void setObjectFromResultSetByRow(ResultSet rs, Object bean){
        Field[] fields = bean.getClass().getDeclaredFields();
        String colName = "";
        try{
            for(int i=0; i<fields.length; i++){
                Field f = fields[i];
                Class<?> type = f.getType();

                TableField tableField = f.getAnnotation(TableField.class);
                if(tableField!=null && StringUtils.hasLength(tableField.value())){
                    colName = tableField.value();
                }else{
                    //默认使用字段名作为
                    colName = f.getName();
                    //colName  = 驼峰命名？
                    colName = StringUtils.propertyNameToColumnName(colName);
                }
                try {
                    Object value = DbCommonUtil.getValueByFieldFromResultSet(colName, type, rs);
                    if(value!=null){
                        ReflectUtils.setFieldValue(bean, colName, value);
                    }
                }catch (Exception e) {
                    logger.error("Exception ",e);
                    continue;
                }
            }
        }catch(Exception e){
            throw new BaseSqlException("set bean property ["+colName+"] error!", e);
        }
    }

    /**
     * 根据 ResultSet 和 bean 实例，设置实例值, 只设置 columnName[] 中的字段值
     * @param rs
     * @param bean
     */
    public static void setObjectFromResultSetByRow(ResultSet rs, Object bean,String ...columnName){
        Field[] fields = bean.getClass().getDeclaredFields();
        String colName = "";
        try{
            for(int i=0; i<fields.length; i++){
                Field f = fields[i];
                Class<?> type = f.getType();

                TableField tableField = f.getAnnotation(TableField.class);
                if(tableField!=null && StringUtils.hasLength(tableField.value())){
                    colName = tableField.value();
                }else{
                    //默认使用字段名作为
                    colName = f.getName();
                    //colName  = 驼峰命名？
                    colName = StringUtils.propertyNameToColumnName(colName);
                }
                if(!ArrayUtils.contains(columnName,colName)){
                    continue;
                }

                Object value = DbCommonUtil.getValueByFieldFromResultSet(colName, type, rs);
                if(value!=null){
                    ReflectUtils.setFieldValue(bean, colName, value);
                }
            }
        }catch(Exception e){
            throw new BaseSqlException("set bean property ["+colName+"] error!", e);
        }
    }

    public static void setBeanFromResultSetByRow(ResultSet rs, Object bean){
        String colName = null;
        try{
            BeanInfo beanInfo = Introspector.getBeanInfo(bean.getClass());
            PropertyDescriptor[] props = beanInfo.getPropertyDescriptors();
            for(PropertyDescriptor prop : props){
                Class<?> type = prop.getPropertyType();

                colName = prop.getName();
                //colName  = 驼峰命名？
                colName = StringUtils.propertyNameToColumnName(colName);

                Object value = DbCommonUtil.getValueByFieldFromResultSet(colName, type, rs);
                if(value!=null){
                    ReflectUtils.setFieldValue(bean, colName, value);
                }
            }
        }catch(Exception e){
            throw new BaseException("set bean property ["+colName+"] error!", e);
        }
    }

    /**
     * 关闭 1）链接 2）statement 3) ResultSet
     * @param con
     * @param stm
     * @param rs
     */
    public static void close(Connection con, Statement stm, ResultSet rs) {
        closeResultSet(rs);
        closeStament(stm);
        closeCon(con);
    }

    public static void closeCon(Connection con) {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException se) {
            logger.error("close connection error!", se);
        }
    }

    public static void closeStament(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException se) {
            logger.error("close Statement error!", se);
        }
    }

    public static void closePreparedStatement(Statement prestmt) {
        try {
            if (prestmt != null) {
                prestmt.close();
            }
        } catch (SQLException se) {
            logger.error("close PreparedStatement error!", se);
        }
    }

    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException se) {
            logger.error("close ResultSet error!", se);
        }
    }

    public static <T> List<T> toList(ResultSet rs, boolean autoClose, ResultSetMapper<T> mapper){
        List<T> datas = new ArrayList<>();

        try {
            T rowMap = null;
            while(rs.next()){
                rowMap = mapper.map(rs);
                if(rowMap!=null) {
                    datas.add(rowMap);
                }
            }
        } catch (Exception e) {
            handleDBException(e);
        } finally{
            if(autoClose) {
                closeResultSet(rs);
            }
        }
        return datas;
    }


    public static Map<String, Object> toMap(ResultSet rs, boolean autoClose, ResultSetMapper<Map<String, Object>> mapper) {
        Map<String, Object> rowMap = null;
        try {
            rowMap = mapper.map(rs);
        } catch (Exception e) {
            handleDBException(e);
        } finally{
            if(autoClose) {
                closeResultSet(rs);
            }
        }
        return rowMap;
    }

    public static List<Map<String, Object>> toList(ResultSet rs, boolean autoClose, String...names){
        List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();

        try {
            Map<String, Object> rowMap = null;
            while(rs.next()){
                rowMap = toMap(rs, false, names);
                if(rowMap!=null) {
                    datas.add(rowMap);
                }
            }
        } catch (Exception e) {
            handleDBException(e);
        } finally{
            if(autoClose) {
                closeResultSet(rs);
            }
        }
        return datas;
    }

    public static void handleDBException(Exception e){
        if(RuntimeException.class.isInstance(e)){
            throw (RuntimeException) e;
        }else if(SQLException.class.isInstance(e)){
            throw new OrmException("sql error.", e);
        }else{
            throw new OrmException("db process error.", e);
        }
    }

    /****
     * autoClose is false
     * @param rs
     * @param names
     * @return
     */
    public static Map<String, Object> toMap(ResultSet rs, String...names) {
        return toMap(rs, true, names);
    }
    public static Map<String, Object> nextRowToMap(ResultSet rs, String...names) throws SQLException {
        if(rs.next()){
            return toMap(rs, true, names);
        }else{
            return Collections.EMPTY_MAP;
        }
    }

    public static Map<String, Object> toMap(ResultSet rs, boolean autoClose, String...names) {
        Map<String, Object> rowMap = new CaseInsensitiveMap<String, Object>();
        try {
            if(names==null || names.length==0){
                Map<String, Integer> columnNames = getColumnMeta(rs);
                Object val = null;
                for(Map.Entry<String, Integer> colName : columnNames.entrySet()){
                    try {
                        int index = colName.getValue()+1;
//						System.out.println("index: " + index);
                        int sqlType = getColumnSqlType(rs, index);
                        val = DbCommonUtil.getValueByFieldFromResultSet(colName.getKey(), JavaTypeMapperJdbcType.getJavaType(sqlType), rs);
                        rowMap.put(colName.getKey().toLowerCase(), val);
                    } catch (Exception e) {
                        throw new ServiceException("get value error : " + colName, e);
                    }
                }
            }else{
                Collection<String> columnNames = CUtils.trimAsList(names);
                Object val = null;
                for(String colName : columnNames){
                    try {
                        val = DbCommonUtil.getResultSetValue(rs, colName);
                        rowMap.put(colName.toLowerCase(), val);
                    } catch (Exception e) {
                        throw new ServiceException("get value error : " + colName, e);
                    }
                }
            }
        } catch (Exception e) {
            handleDBException(e);
        } finally{
            if(autoClose) {
                closeResultSet(rs);
            }
        }
        return rowMap;
    }

    public static Map<String, Integer> getColumnMeta(ResultSet rs) throws SQLException {
        ResultSetMetaData rsMeta = rs.getMetaData();
        int colCount = rsMeta.getColumnCount();
        Map<String, Integer> column = new CaseInsensitiveMap<>();
        for(int i=1; i<=colCount; i++){
            column.put(rsMeta.getColumnName(i), i-1);
        }
        return column;
    }

    public static String getColumnName(ResultSet rs, int colIndex) throws SQLException{
        ResultSetMetaData rsMeta = rs.getMetaData();
        return rsMeta.getColumnName(colIndex);
    }

    public static int getColumnSqlType(ResultSet rs, int columnIndex) throws SQLException{
        ResultSetMetaData rsMeta = rs.getMetaData();
        return rsMeta.getColumnType(columnIndex);
    }

}
